package lumis.content.workflow;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import lumis.portal.PortalException;
import lumis.portal.PortalObjectAlreadyExistsException;
import lumis.portal.PortalObjectNotFoundException;
import lumis.portal.UnexpectedException;
import lumis.portal.dao.hibernate.ITransactionHibernate;
import lumis.portal.dao.jdbc.ITransactionJdbc;
import lumis.portal.transaction.PortalTransactionFactory;
import lumis.util.ITransaction;
import lumis.util.JdbcUtil;
import lumis.util.PortalUtil;

/**
 * Dao JDBC implementation for Workflow related data
 *
 * @version $Revision: 11764 $ $Date: 2010-07-24 14:04:01 -0300 (Sat, 24 Jul 2010) $
 * @since 4.0.0
 */
class WorkflowDaoJdbc implements IWorkflowDao
{
	public void add(WorkflowConfig workflowConfig, ITransaction daoTransaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) daoTransaction;
			Connection connection = daoTransactionJdbc.getConnection();
	
			PreparedStatement statement = connection.prepareStatement("insert into lum_Workflow (workflowId, name, description, baseUrl, stringsUrlPrefix, definition) values (?,?,?,?,?,?)");
			try
			{
				statement.setString(1, workflowConfig.getId());
				statement.setString(2, workflowConfig.getName());
				statement.setString(3, workflowConfig.getDescription());
				statement.setString(4, workflowConfig.getBaseUrl());
				statement.setString(5, workflowConfig.getStringsUrlPrefix());
				statement.setString(6, workflowConfig.getDefinition());
				try
				{
					statement.execute();
				}
				catch (Exception e) 
				{
					try
					{
						if(get(workflowConfig.getId(), daoTransaction) != null)
							throw new PortalObjectAlreadyExistsException("STR_RESOURCE_ALREADY_EXISTS;" + workflowConfig.getId(), e);
						else
							throw e;
					}
					catch (PortalObjectNotFoundException e1)
					{
						throw e;
					}
				}
			}
			finally
			{
				statement.close();
			}
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}

	public void update(WorkflowConfig workflowConfig, ITransaction daoTransaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) daoTransaction;
			Connection connection = daoTransactionJdbc.getConnection();
	
			PreparedStatement statement = connection.prepareStatement("update lum_Workflow set name=?, description=?, baseUrl=?, stringsUrlPrefix=?, definition=? where workflowId=?");
			try
			{
				statement.setString(1, workflowConfig.getName());
				statement.setString(2, workflowConfig.getDescription());
				statement.setString(3, workflowConfig.getBaseUrl());
				statement.setString(4, workflowConfig.getStringsUrlPrefix());
				statement.setString(5, workflowConfig.getDefinition());
				statement.setString(6, workflowConfig.getId());

				statement.executeUpdate();
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}	

	public WorkflowConfig get(String workflowId, ITransaction daoTransaction) throws PortalException
	{
		try
		{
			String strQuery = "SELECT definition FROM lum_Workflow WHERE workflowId=?";
	
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) daoTransaction;
			Connection connection = daoTransactionJdbc.getConnection();
			PreparedStatement statement = connection.prepareStatement(strQuery);
			try
			{
				statement.setString(1, workflowId);
				ResultSet resultSet = statement.executeQuery();
				try
				{
					if (resultSet.next())
					{
						WorkflowConfig workflowConfig = new WorkflowConfig();
						workflowConfig.setDefinition(resultSet.getString("definition"));
						return workflowConfig;
					}
					else
						throw new WorkflowException("STR_WORKFLOW_NOT_FOUND;" + workflowId);
				}
				finally
				{
					resultSet.close();					
				}
			}
			finally
			{
				statement.close();				
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}
		
	public boolean delete(String workflowId, ITransaction transaction) throws PortalException
	{
		try
		{
			if ("lumis.standardworkflow".equals(workflowId))
				throw new WorkflowException("STR_STANDARD_WORKFLOW_CANNOT_BE_DELETED");
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc)transaction;
			Connection connection = daoTransactionJdbc.getConnection();
	
			PreparedStatement statement = connection.prepareStatement("delete from lum_Workflow where workflowId=?");
			try
			{
				statement.setString(1, workflowId);
				int rowsAffected = statement.executeUpdate();
				
				if (rowsAffected == 0)
					return false;
				else if (rowsAffected == 1)
					return true;
				else
					throw new WorkflowException("STR_MORE_THAN_ONE_ROW_DELETED");
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}

	public String getIdByServiceInstance(String serviceInstanceId, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("select workflowId from lum_WorkflowServiceInstance where serviceInstanceId=?");
			try
			{
				statement.setString(1, serviceInstanceId);
				ResultSet resultSet = statement.executeQuery();
				try
				{
					if (resultSet.next())
						return resultSet.getString(1);
					else
						return null;
				}
				finally
				{
					resultSet.close();
				}
			}
			finally
			{
				statement.close();				
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}
	
	public synchronized void setServiceInstanceWorkflow(String serviceInstanceId, String workflowId, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			
			if (serviceInstanceId == null)
				throw new NullPointerException("serviceInstanceId cannot be null");
			
			if (workflowId != null)
			{
				// verify if workflowId is different from database. If not, exit method, nothing should be changed.
				PreparedStatement stSelect = connection.prepareStatement("select workflowId from lum_WorkflowServiceInstance where serviceInstanceId=?");
				try
				{
					stSelect.setString(1, serviceInstanceId);
					ResultSet resultSet = stSelect.executeQuery();
					try
					{
						if (resultSet.next())
						{
							String workflowIdInDB = resultSet.getString(1);
							if (workflowId.equals(workflowIdInDB))
								return; // no change should be done
						}
					}
					finally
					{
						resultSet.close();
					}
				}
				finally
				{
					stSelect.close();
				}
				
				// try to change the workflow id
				PreparedStatement stUpdate = connection.prepareStatement("update lum_WorkflowServiceInstance set workflowId=? where serviceInstanceId=?");
				int rowsUpdated;
				try
				{
					stUpdate.setString(1, workflowId);
					stUpdate.setString(2, serviceInstanceId);
					rowsUpdated = stUpdate.executeUpdate();
				}
				finally
				{
					stUpdate.close();
				}
				
				// no entry for that service instance exists - must insert a new entry for it
				if (rowsUpdated == 0)
				{
					PreparedStatement stInsert = connection.prepareStatement("insert into lum_WorkflowServiceInstance (id, workflowId, serviceInstanceId) values (?,?,?)");
					try
					{
						stInsert.setString(1, PortalUtil.generateNewGuid());
						stInsert.setString(2, workflowId);
						stInsert.setString(3, serviceInstanceId);
						stInsert.executeUpdate();
					}
					finally
					{
						stInsert.close();
					}				
				}				
			}
			else
			{
				// delete permissions for that workflowServiceInstance
				setWorkflowPermissions(serviceInstanceId, Collections.<WorkflowPermission>emptyList(), transaction);
				
				// delete the workflowServiceInstance
				PreparedStatement stDelete = connection.prepareStatement("delete from lum_WorkflowServiceInstance where serviceInstanceId=?");
				try
				{
					stDelete.setString(1, serviceInstanceId);
					stDelete.executeUpdate();
				}
				finally
				{
					stDelete.close();
				}
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}		
	}
	
	public Collection<String> getIds(ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("select workflowId from lum_Workflow");
			try
			{
				return JdbcUtil.fillListString(statement, "workflowId");
			}
			finally
			{
				statement.close();				
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}
	
	public Collection<WorkflowPermission> getWorkflowPermissions(String serviceInstanceId, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();

			PreparedStatement statement = connection.prepareStatement("select lum_WorkflowServiceInstance.workflowId, lum_WorkflowServiceInstance.serviceInstanceId, lum_WorkflowPermission.role, lum_WorkflowPermission.principalId, lum_WorkflowPermission.type " +
					"from lum_WorkflowPermission, lum_WorkflowServiceInstance where lum_WorkflowPermission.workflowServiceInstanceId = lum_WorkflowServiceInstance.id and lum_WorkflowServiceInstance.serviceInstanceId = ?");
			try
			{
				statement.setString(1, serviceInstanceId);
				ResultSet resultSet = statement.executeQuery();
				try
				{
					Collection<WorkflowPermission> permissions = new ArrayList<WorkflowPermission>();
					while(resultSet.next())
					{
						WorkflowPermission permission = new WorkflowPermission();
						permission.setWorkflowId(resultSet.getString(1));
						permission.setServiceInstanceId(resultSet.getString(2));
						permission.setRole(resultSet.getString(3));
						permission.setPrincipalId(resultSet.getString(4));
						permission.setType(resultSet.getInt(5));
						permissions.add(permission);
					}
					
					return permissions;
				}
				finally
				{
					resultSet.close();
				}
			}
			finally
			{
				statement.close();				
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}		
	}
	
	public void setWorkflowPermissions(String serviceInstanceId, Collection<WorkflowPermission> permissions, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			
			if (serviceInstanceId == null)
				throw new NullPointerException("serviceInstanceId cannot be null");

			// get workflowServiceInstanceId
			String workflowServiceInstanceId;
			PreparedStatement stSelect = connection.prepareStatement("select id from lum_WorkflowServiceInstance where serviceInstanceId=?");
			try
			{
				stSelect.setString(1, serviceInstanceId);
				ResultSet resultSet = stSelect.executeQuery();
				try
				{
					if (resultSet.next())
						workflowServiceInstanceId = resultSet.getString(1);
					else
						throw new WorkflowException("SERVICE_INSTANCE_DOES_NOT_HAVE_WORKFLOW");
				}
				finally
				{
					resultSet.close();
				}
			}
			finally
			{
				stSelect.close();
			}

			// delete old permissions
			PreparedStatement stDelete = connection.prepareStatement("delete from lum_WorkflowPermission where workflowServiceInstanceId = ?");
			try
			{
				stDelete.setString(1, workflowServiceInstanceId);
				stDelete.executeUpdate();
			}
			finally
			{
				stDelete.close();
			}

			// insert permissions given
			PreparedStatement stInsert = connection.prepareStatement("insert into lum_WorkflowPermission (id, workflowServiceInstanceId, principalId, role, type) values (?,?,?,?,?)");
			try
			{
				for (WorkflowPermission permission: permissions)
				{
					stInsert.setString(1, PortalUtil.generateNewGuid());
					stInsert.setString(2, workflowServiceInstanceId);
					stInsert.setString(3, permission.getPrincipalId());
					stInsert.setString(4, permission.getRole());
					stInsert.setInt(5, permission.getType());
					stInsert.executeUpdate();
				}
			}
			finally
			{
				stInsert.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}				
	}
		
	public boolean hasPermission(String serviceInstanceId, Collection<String> principals, Collection<String> permissions, int permissionType, ITransaction transaction) throws PortalException
	{
		if (principals.isEmpty() || permissions.isEmpty())
			return false;
		
		StringBuilder sql = new StringBuilder(400);
		
		sql.append("SELECT COUNT(*) FROM lum_WorkflowPermission INNER JOIN lum_WorkflowServiceInstance ON lum_WorkflowPermission.workflowServiceInstanceId = lum_WorkflowServiceInstance.id WHERE ");
		sql.append(JdbcUtil.generateInClause("lum_WorkflowPermission.principalId", principals.size()) + " AND ");
		sql.append(JdbcUtil.generateInClause("lum_WorkflowPermission.role", permissions.size()));
		sql.append(" AND lum_WorkflowPermission.type = ? AND lum_WorkflowServiceInstance.serviceInstanceId = ?");
		
		try
		{	
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			PreparedStatement statement = connection.prepareStatement(sql.toString());
			try
			{
				int parameterIndex = 1;
				for (String principalId: principals)
					statement.setString(parameterIndex++, principalId);
				for (String permission: permissions)
					statement.setString(parameterIndex++, permission);
				statement.setInt(parameterIndex++, permissionType);
				statement.setString(parameterIndex++, serviceInstanceId);
				ResultSet resultSet = statement.executeQuery();
				try
				{
					if (resultSet.next())
					{
						int count = resultSet.getInt(1);
						return count > 0;
					}
					else
						return false;
				}
				finally
				{
					resultSet.close();					
				}
			}
			finally
			{
				statement.close();				
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}
	
	public Collection<String> getRolesByPrincipals(String serviceInstanceId, Collection<String> principals, ITransaction transaction) throws PortalException
	{
		if (principals.isEmpty())
			return Collections.<String>emptyList();
		
		StringBuilder principalsSql = new StringBuilder(principals.size() * 2);
		principalsSql.append("?");
		for (int i=0; i<principals.size()-1; i++)
			principalsSql.append(",?");
		
		StringBuilder sql = new StringBuilder(600);
		
		sql.append("SELECT lum_WorkflowPermission.role FROM lum_WorkflowServiceInstance INNER JOIN lum_WorkflowPermission ON " +
					"lum_WorkflowServiceInstance.id = lum_WorkflowPermission.workflowServiceInstanceId WHERE lum_WorkflowServiceInstance.serviceInstanceId = ? AND ");
		sql.append(JdbcUtil.generateInClause("lum_WorkflowPermission.principalId", principals.size()));		
		sql.append(" AND lum_WorkflowPermission.type = " + WorkflowPermission.PERMISSION_TYPE_ALLOW + " AND lum_WorkflowPermission.role NOT IN " +
					"(SELECT role FROM lum_WorkflowPermission wp WHERE wp.workflowServiceInstanceId = lum_WorkflowServiceInstance.id AND ");
		sql.append(JdbcUtil.generateInClause("principalId", principals.size()));
		sql.append(" AND type = " + WorkflowPermission.PERMISSION_TYPE_DENY + ")");
		
		try
		{	
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			PreparedStatement statement = connection.prepareStatement(sql.toString());
			try
			{
				int parameterIndex = 1;
				statement.setString(parameterIndex++, serviceInstanceId);
				for (String principalId: principals)
					statement.setString(parameterIndex++, principalId);
				for (String principalId: principals)
					statement.setString(parameterIndex++, principalId);
				Collection<String> roles = JdbcUtil.fillListString(statement, "role");
				return roles;
			}
			finally
			{
				statement.close();				
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}
	
	public ContentWorkflowMetaData getContentWorkflowMetaData(String contentLocaleId, ITransaction daoTransaction) throws PortalException
	{
		try
		{
			String strQuery = "SELECT workflowMetaDataId, contentLocaleId, stateId, assignedTo FROM lum_WorkflowMetaData WHERE contentLocaleId=?";
	
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) daoTransaction;
			Connection connection = daoTransactionJdbc.getConnection();
			PreparedStatement statement = connection.prepareStatement(strQuery);
			try
			{
				statement.setString(1, contentLocaleId);
				ResultSet resultSet = statement.executeQuery();
				try
				{
					if (resultSet.next())
					{
						ContentWorkflowMetaData workflowMetaData = new ContentWorkflowMetaData();
						workflowMetaData.setWorkflowMetaDataId(resultSet.getString("workflowMetaDataId"));
						workflowMetaData.setContentLocaleId(resultSet.getString("contentLocaleId"));
						workflowMetaData.setStateId(resultSet.getString("stateId"));
						workflowMetaData.setAssignedTo(resultSet.getString("assignedTo"));
						return workflowMetaData;
					}
					else
						return null;
				}
				finally
				{
					resultSet.close();					
				}
			}
			finally
			{
				statement.close();				
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}
	
	public boolean deleteContentWorkflowMetaData(String contentLocaleId, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc)transaction;
			Connection connection = daoTransactionJdbc.getConnection();
	
			PreparedStatement statement = connection.prepareStatement("delete from lum_WorkflowMetaData where contentLocaleId=?");
			try
			{
				statement.setString(1, contentLocaleId);
				int rowsAffected = statement.executeUpdate();
				
				if (rowsAffected == 0)
					return false;
				else if (rowsAffected == 1)
					return true;
				else
					throw new WorkflowException("STR_MORE_THAN_ONE_ROW_DELETED");
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}	
	
	public void add(ContentWorkflowMetaData contentWorkflowMetaData, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
	
			PreparedStatement statement = connection.prepareStatement("insert into lum_WorkflowMetaData (workflowMetaDataId, contentLocaleId, stateId, assignedTo) values (?,?,?,?)");
			try
			{
				statement.setString(1, contentWorkflowMetaData.getWorkflowMetaDataId());
				statement.setString(2, contentWorkflowMetaData.getContentLocaleId());
				statement.setString(3, contentWorkflowMetaData.getStateId());
				statement.setString(4, contentWorkflowMetaData.getAssignedTo());
			
				try
				{
					statement.execute();
				}
				catch (Exception e) 
				{
					try
					{
						if(getContentWorkflowMetaData(contentWorkflowMetaData.getContentLocaleId(), transaction) != null)
							throw new PortalObjectAlreadyExistsException("STR_RESOURCE_ALREADY_EXISTS;" + contentWorkflowMetaData.getWorkflowMetaDataId(), e);
						else
							throw e;
					}
					catch (PortalObjectNotFoundException e1)
					{
						throw e;
					}
				}
			}
			finally
			{
				statement.close();
			}
		}
		catch (PortalException e)
		{
			throw e;
		}
		catch (Exception e)
		{
			throw new UnexpectedException(e);
		}
	}
	
	public void update(ContentWorkflowMetaData contentWorkflowMetaData, ITransaction daoTransaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) daoTransaction;
			Connection connection = daoTransactionJdbc.getConnection();
	
			PreparedStatement statement = connection.prepareStatement("update lum_WorkflowMetaData set stateId=?, assignedTo=? where workflowMetaDataId=?");
			try
			{
				statement.setString(1, contentWorkflowMetaData.getStateId());
				statement.setString(2, contentWorkflowMetaData.getAssignedTo());
				statement.setString(3, contentWorkflowMetaData.getWorkflowMetaDataId());

				statement.executeUpdate();
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}		
	}
	
	public Collection<String> getPrincipalIdByServiceInstanceIdAndRoles(String serviceInstanceId, Collection<String> roles, int permissionType, ITransaction transaction) throws PortalException
	{
		if (roles.isEmpty())
			return Collections.<String>emptyList();
		
		StringBuilder sql = new StringBuilder(400);
		
		sql.append("SELECT DISTINCT principalId FROM lum_WorkflowPermission INNER JOIN lum_WorkflowServiceInstance ON " +
					"lum_WorkflowPermission.workflowServiceInstanceId = lum_WorkflowServiceInstance.id WHERE ");
		sql.append(JdbcUtil.generateInClause("lum_WorkflowPermission.role", roles.size()));
		sql.append(" AND lum_WorkflowPermission.type = ? AND lum_WorkflowServiceInstance.serviceInstanceId = ?");
		
		try
		{	
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			PreparedStatement statement = connection.prepareStatement(sql.toString());
			try
			{
				int parameterIndex = 1;
				for (String role: roles)
					statement.setString(parameterIndex++, role);
				statement.setInt(parameterIndex++, permissionType);
				statement.setString(parameterIndex++, serviceInstanceId);
				List<String> principalIds = JdbcUtil.fillListString(statement, "principalId");
				return principalIds;
			}
			finally
			{
				statement.close();				
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}
	
	public boolean serviceInstanceHasAnyContentWorkflowMetaData(String serviceInstanceId, ITransaction transaction) throws PortalException
	{
		try
		{	
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) transaction;
			Connection connection = daoTransactionJdbc.getConnection();
			PreparedStatement statement = connection.prepareStatement("SELECT COUNT(*) FROM lum_WorkflowMetaData INNER JOIN lum_ContentLocale ON lum_WorkflowMetaData.contentLocaleId = lum_ContentLocale.id INNER JOIN lum_Content ON lum_ContentLocale.contentId = lum_Content.id WHERE lum_Content.serviceInstanceId = ?");
			try
			{
				statement.setString(1, serviceInstanceId);
				ResultSet resultSet = statement.executeQuery();
				try
				{
					if (resultSet.next())
					{
						int count = resultSet.getInt(1);
						return count > 0;
					}
					else
						return false;
				}
				finally
				{
					resultSet.close();				
				}
			}
			finally
			{
				statement.close();				
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
		
	}
	
	public void deleteAllContentWorkflowMetaData(String serviceInstanceId, ITransaction transaction) throws PortalException
	{
		try
		{
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc)transaction;
			Connection connection = daoTransactionJdbc.getConnection();
				
			// delete content workflow metadata
			PreparedStatement statement = connection.prepareStatement("delete from lum_WorkflowMetaData where contentLocaleId in (select lum_ContentLocale.id from lum_ContentLocale INNER JOIN lum_Content ON lum_ContentLocale.contentId = lum_Content.id where lum_Content.serviceInstanceId=?)");
			try
			{
				statement.setString(1, serviceInstanceId);
				statement.executeUpdate();
			}
			finally
			{
				statement.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}
	
	@SuppressWarnings("unchecked")
	public Collection<String> getSourceIdsWithContent(String serviceInstanceId, ITransaction transaction) throws PortalException
	{
		return ((ITransactionHibernate)transaction).getSession().createQuery(
				"select distinct c.sourceId from Content c where c.serviceInstanceId = ?")
				.setString(0, serviceInstanceId)
				.list();
	}
	
	public void removeWorkflowPermissionsWithoutAcl() throws PortalException
	{
		ITransactionJdbc transaction = (ITransactionJdbc)PortalTransactionFactory.getCurrentTransaction();
		try
		{
			PreparedStatement ps = transaction.getConnection().prepareStatement(
					"DELETE FROM lum_WorkflowPermission WHERE (NOT EXISTS " +
					"(SELECT lum_WorkflowServiceInstance.id FROM lum_WorkflowServiceInstance INNER JOIN " +
					"lum_ServiceInstance ON lum_WorkflowServiceInstance.serviceInstanceId = lum_ServiceInstance.serviceInstanceId INNER JOIN " +
					"lum_AccessControlList ON lum_ServiceInstance.accessControlListId = lum_AccessControlList.accessControlListId INNER JOIN " +
					"lum_AccessControlEntry ON lum_AccessControlList.accessControlListId = lum_AccessControlEntry.accessControlListId " +
					"WHERE (lum_AccessControlEntry.principalId = lum_WorkflowPermission.principalId) AND " +
					"(lum_WorkflowServiceInstance.id = lum_WorkflowPermission.workflowServiceInstanceId)))");
			try
			{
				ps.executeUpdate();
			}
			finally
			{
				ps.close();
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}

	public Map<String, String> getContentMetadataPrincipals(String serviceInstanceId, ITransaction daoTransaction) throws PortalException
	{
		try
		{
			HashMap<String, String> results = new HashMap<String, String>();
			
			String strQuery = "SELECT DISTINCT w.assignedTo, p.name FROM lum_WorkflowMetaData w "
				+ " INNER JOIN lum_ContentLocale cl ON cl.id = w.contentLocaleId "
				+ " INNER JOIN lum_Content c ON cl.contentId = c.id "
				+ " INNER JOIN lum_Principal p ON w.assignedTo = p.principalId "
				+ " WHERE c.serviceInstanceId = ?"
				+ " order by p.name ";
	
			ITransactionJdbc daoTransactionJdbc = (ITransactionJdbc) daoTransaction;
			Connection connection = daoTransactionJdbc.getConnection();
			PreparedStatement statement = connection.prepareStatement(strQuery);
			try
			{
				statement.setString(1, serviceInstanceId);
				ResultSet resultSet = statement.executeQuery();
				try
				{
					while (resultSet.next())
						results.put(resultSet.getString("assignedTo"), resultSet.getString("name"));						

					return results;
				}
				finally
				{
					resultSet.close();					
				}
			}
			finally
			{
				statement.close();				
			}
		}
		catch (SQLException e)
		{
			throw new UnexpectedException(e);
		}
	}
}
